﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using SPA_Prototype.Model;
using System.Diagnostics;

using ExcelCOM = Microsoft.Office.Interop.Excel;

namespace SPA_Prototype
{        
    public class Export2Excel
    {
        // public string ApplicationRunningPath;

        private History _History;
        private DatabaseDataSet _Dataset;

        private DatabaseDataSet.BillMainTableDataTable _MainDataTable;
        private DatabaseDataSet.BillDetailsTableDataTable _DetailsDataTable;
        private DatabaseDataSet.ServiceTableDataTable _ServiceDataTable;      

        private DatabaseDataSetTableAdapters.BillMainTableTableAdapter _AdtMain;
        private DatabaseDataSetTableAdapters.BillDetailsTableTableAdapter _AdtDetails;
        private DatabaseDataSetTableAdapters.ServiceTableTableAdapter _AdtService;

        int     _NumberOfBill = 0;
        int     _NumberOfService = 0;
        int     _StartRow = 10;
        int     _StartColumn = 17;

        private List<string> _ServiceID;
        private List<string> _ServicePrice;
        private List<string> _BillNo;
        private List<string> _ServiceInBill;

        public void ExportFromDatgridView(DataGridView dtg, string exTemplatePath, string exSheetName)
        {
            object missValue = System.Reflection.Missing.Value;

            ExcelCOM.Application exApp = new ExcelCOM.Application();
            ExcelCOM.Workbook exBook = exApp.Workbooks.Open(exTemplatePath, 0, false, 5, "", "", false, ExcelCOM.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

            ExcelCOM.Worksheet exSheet = (ExcelCOM.Worksheet)exBook.Worksheets[1];
            if(exSheetName != null)  exSheet.Name = exSheetName;

            exApp.Visible = false;
            SaveFileDialog f = new SaveFileDialog();
            f.Filter = "Excel file (*.xls)|*.xls";
            bool saved = false;
            if (f.ShowDialog() == DialogResult.OK)
            {
                int rowCount = dtg.RowCount;
                int columnCount = dtg.ColumnCount;
                int row;
                int column;
                // fill column headers
                for (column = 0; column < columnCount; column++) exSheet.Cells[3, column + 2] = dtg.Columns[column].HeaderText;
                // fill data
                for (row = 0; row < rowCount; row++)
                    for (column = 0; column < columnCount; column++)
                        exSheet.Cells[row + 4, column + 2] = dtg.Rows[row].Cells[column].Value;
                exBook.SaveAs(f.FileName, ExcelCOM.XlFileFormat.xlWorkbookNormal, missValue, missValue, missValue, missValue, ExcelCOM.XlSaveAsAccessMode.xlExclusive, missValue, missValue, missValue, missValue, missValue);
                saved = true;
            }
            exBook.Close(true, missValue, missValue);
            exApp.Quit();

            releaseObject(exSheet);
            releaseObject(exBook);
            releaseObject(exApp);

            if (saved)
            {
                var result = MessageBox.Show("Sucessful \r\n Do you want to view the File ?", "Done", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly);
                if (result == System.Windows.Forms.DialogResult.Yes)
                {
                Process.Start(f.FileName);
                }
            }
        }


        public void ExportFromDatabse(DatabaseDataSet.BillMainTableDataTable tb, string exTemplatePath, string exSheetName) 
        {
            _History = new History();
            _Dataset = new DatabaseDataSet();

            _ServiceID = new List<string>();
            _ServicePrice = new List<string>();
            _BillNo = new List<string>();
            _ServiceInBill = new List<string>();

            // _MainDataTable = new DatabaseDataSet.BillMainTableDataTable(); //_Dataset.BillMainTable;
            _MainDataTable = tb;
            _DetailsDataTable =  new DatabaseDataSet.BillDetailsTableDataTable();  //Dataset.BillDetailsTable;
            _ServiceDataTable = new DatabaseDataSet.ServiceTableDataTable(); //_Dataset.ServiceTable;

            _AdtMain = new DatabaseDataSetTableAdapters.BillMainTableTableAdapter();
            _AdtDetails = new DatabaseDataSetTableAdapters.BillDetailsTableTableAdapter();
            _AdtService = new DatabaseDataSetTableAdapters.ServiceTableTableAdapter();

            LoadDB();
            InitData();
            ExportReport(exTemplatePath, exSheetName);
        }
        // medthod : load database, init data
        private void LoadDB()
        {
            // _MainDataTable = _History.Search(SEARCH_METHOD.ALL, TIME_CONSTRAIN.TODAY, "");                        
            _AdtService.Fill(_ServiceDataTable);
        }

        private void InitData()
        {
            _NumberOfBill = _MainDataTable.Rows.Count;
            _NumberOfService = _ServiceDataTable.Rows.Count;

            for (int i = 0; i < _NumberOfBill; i++)
            {
                _BillNo.Add(_MainDataTable[i].Number);
            }
            for (int i = 0; i < _NumberOfService; i++)
            {
                _ServiceID.Add(_ServiceDataTable[i].ID);
                _ServicePrice.Add(_ServiceDataTable[i].Price.ToString());
            }
        }


        private void ExportReport(string exTemplatePath, string exSheetName)
        {
            object missValue = System.Reflection.Missing.Value;
   
            ExcelCOM.Application exApp = new ExcelCOM.Application();                     
            ExcelCOM.Workbook exBook = exApp.Workbooks.Open(exTemplatePath, 0, false, 5, "", "", false, ExcelCOM.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

            ExcelCOM.Worksheet exSheet = (ExcelCOM.Worksheet)exBook.Worksheets[1];
            if (exSheetName != null) exSheet.Name = exSheetName;
                       
            exApp.Visible = false;
            SaveFileDialog f = new SaveFileDialog();
            f.Filter = "Excel file (*.xls)|*.xls";
            bool saved = false;
            if (f.ShowDialog() == DialogResult.OK)
            {                
                int endColumn;

                int row;
                int column;                            
         
                endColumn = _StartColumn + _NumberOfService*2; // each service needs 2 sucessive cells in a row
                // Fill Issue date
                exSheet.Cells[3, 2] = DateTime.Now;
                //Fill Service Name & Current Unit Price
                column = _StartColumn;
                for (int i=0; i<_NumberOfService;i++ )
                {                                                                  
                    exSheet.Cells[_StartRow, column] = _ServiceID[i] ;
                    exSheet.Cells[_StartRow + 1, column] = _ServicePrice[i] ;
                    column += 2;
                }

                //Fill data to each row  
                for (int i = 0; i < _NumberOfBill; i++) // i: data row number i
                {
                    //Fill sequence - not finished


                    // Fill Bill numbers and general Information    
                    row = _StartRow + 2 + i;
                    column = 2;
                    int mainDataTableColumnCount = _MainDataTable.Columns.Count;
                    for (int j = 0; j < mainDataTableColumnCount; j++)
                    {
                        exSheet.Cells[row, column + j] = _MainDataTable[i][j].ToString();                        
                    }
                    // END -- Fill Bill numbers and general Information 

                    // fill Service Quantity and Price for bill i Excel Row
                    column = _StartColumn;
                    _DetailsDataTable = _History.BillDetails(_BillNo[i]);
                    string id;
                    int numberOfServiceInBill = _DetailsDataTable.Rows.Count;                   
                    for (int j = 0; j < numberOfServiceInBill; j++)
                    {
                        id = _DetailsDataTable[j].ServiceID;                      
                        for (int k = 0; k < _NumberOfService; k++)
                        {
                            if (id == _ServiceID[k])  // Compare current ID with ID filled in excel one by one 
                            {
                                column += 2*k;
                                exSheet.Cells[row, column] = _DetailsDataTable[j].Quantity.ToString(); // Fill quantity
                                exSheet.Cells[row, column + 1] = _DetailsDataTable[j].Price.ToString(); // Fill Price
                                break;
                            }
                        }
                       // index = _ServiceDataTable.Columns["ID"].
                    }
                    // END -- Fill Service Quantity and Price for bill i Excel Row
                }
                exBook.SaveAs(f.FileName, ExcelCOM.XlFileFormat.xlWorkbookNormal, missValue, missValue, missValue, missValue, ExcelCOM.XlSaveAsAccessMode.xlExclusive, missValue, missValue, missValue, missValue, missValue);
                saved = true;
            }
            
            exBook.Close(true, missValue, missValue);
            exApp.Quit();

            releaseObject(exSheet);
            releaseObject(exBook);
            releaseObject(exApp);

            if (saved)
            {
                var result = MessageBox.Show("Sucessful \r\n Do you want to view the File ?", "Done", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly);
                if (result == System.Windows.Forms.DialogResult.Yes)
                {
                    Process.Start(f.FileName);
                } 
            }
        }

        // private functions
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                throw new Exception("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
      
    }
}